--    Program: 		CAPPOIMP_POSITION_B.sql
--    Description:  Import PO POSITION information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_position_id            number;
   l_job_id                 number;
                    		                 		
   cursor cur_per_all_positions is
      select * from CRST_PER_ALL_POSITIONS
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_per_all_positions loop 
     l_err_msg_tmp := '';
     
     select PER_POSITIONS_S.NEXTVAL
   	 into   l_position_id
   	 from   dual;  	
     
     update CRST_PER_ALL_POSITIONS
     set    position_id_n = l_position_id
     where  position_id = cur.POSITION_ID;
     
     l_job_id := null; 	
     if cur.JOB_ID is not null then
       begin
         SELECT job_id_n
         INTO   l_job_id
         FROM   CRST_PER_JOBS
         WHERE  job_id = cur.JOB_ID;
       exception
         when others then
           l_err_msg_tmp := 'New Job ID '||cur.JOB_ID||' not found';
       end;
     end if;    	  
     
     begin
       -- import data
       insert into PER_ALL_POSITIONS
       (
            POSITION_ID                ,
            BUSINESS_GROUP_ID          ,
            JOB_ID                     ,
            ORGANIZATION_ID            ,
            SUCCESSOR_POSITION_ID      ,
            RELIEF_POSITION_ID         ,
            LOCATION_ID                ,
            POSITION_DEFINITION_ID     ,
            DATE_EFFECTIVE             ,
            COMMENTS                   ,
            DATE_END                   ,
            FREQUENCY                  ,
            NAME                       ,
            PROBATION_PERIOD           ,
            PROBATION_PERIOD_UNITS     ,
            REPLACEMENT_REQUIRED_FLAG  ,
            TIME_NORMAL_FINISH         ,
            TIME_NORMAL_START          ,
            WORKING_HOURS              ,
            REQUEST_ID                 ,
            PROGRAM_APPLICATION_ID     ,
            PROGRAM_ID                 ,
            PROGRAM_UPDATE_DATE        ,
            ATTRIBUTE_CATEGORY         ,
            ATTRIBUTE1                 ,
            ATTRIBUTE2                 ,
            ATTRIBUTE3                 ,
            ATTRIBUTE4                 ,
            ATTRIBUTE5                 ,
            ATTRIBUTE6                 ,
            ATTRIBUTE7                 ,
            ATTRIBUTE8                 ,
            ATTRIBUTE9                 ,
            ATTRIBUTE10                ,
            ATTRIBUTE11                ,
            ATTRIBUTE12                ,
            ATTRIBUTE13                ,
            ATTRIBUTE14                ,
            ATTRIBUTE15                ,
            ATTRIBUTE16                ,
            ATTRIBUTE17                ,
            ATTRIBUTE18                ,
            ATTRIBUTE19                ,
            ATTRIBUTE20                ,
            STATUS                     ,
            OBJECT_VERSION_NUMBER      ,
            LAST_UPDATE_DATE           ,
            LAST_UPDATED_BY            ,
            LAST_UPDATE_LOGIN          ,
            CREATED_BY                 ,
            CREATION_DATE              
       )
       values
       (
            l_position_id                  ,
            CAPBRIMP_MAPPING.GetBGMapping(cur.BUSINESS_GROUP_ID),
            l_job_id                      ,
            CAPBRIMP_MAPPING.GetBGMapping(cur.ORGANIZATION_ID),
            cur.SUCCESSOR_POSITION_ID      ,
            cur.RELIEF_POSITION_ID         ,
            CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.LOCATION_ID),
            cur.POSITION_DEFINITION_ID     ,
            cur.DATE_EFFECTIVE             ,
            cur.COMMENTS                   ,
            cur.DATE_END                   ,
            cur.FREQUENCY                  ,
            cur.NAME						,
            cur.PROBATION_PERIOD           ,
            cur.PROBATION_PERIOD_UNITS     ,
            cur.REPLACEMENT_REQUIRED_FLAG  ,
            cur.TIME_NORMAL_FINISH         ,
            cur.TIME_NORMAL_START          ,
            cur.WORKING_HOURS              ,
            cur.REQUEST_ID                 ,
            cur.PROGRAM_APPLICATION_ID     ,
            cur.PROGRAM_ID                 ,
            cur.PROGRAM_UPDATE_DATE        ,
            cur.ATTRIBUTE_CATEGORY         ,
            cur.ATTRIBUTE1                 ,
            cur.ATTRIBUTE2                 ,
            cur.ATTRIBUTE3                 ,
            cur.ATTRIBUTE4                 ,
            cur.ATTRIBUTE5                 ,
            cur.ATTRIBUTE6                 ,
            cur.ATTRIBUTE7                 ,
            cur.ATTRIBUTE8                 ,
            cur.ATTRIBUTE9                 ,
            cur.ATTRIBUTE10                ,
            cur.ATTRIBUTE11                ,
            cur.ATTRIBUTE12                ,
            cur.ATTRIBUTE13                ,
            cur.ATTRIBUTE14                ,
            cur.ATTRIBUTE15                ,
            cur.ATTRIBUTE16                ,
            cur.ATTRIBUTE17                ,
            cur.ATTRIBUTE18                ,
            cur.ATTRIBUTE19                ,
            cur.ATTRIBUTE20                ,
            cur.STATUS                     ,
            cur.OBJECT_VERSION_NUMBER      ,
            cur.LAST_UPDATE_DATE           ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.LAST_UPDATE_LOGIN          ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.CREATION_DATE              
       ); 
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 --dbms_output.put_line('2');
   	 commit;
   else
     --dbms_output.put_line('4');
     rollback;
   end if;
      
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_POSITION_B', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   dbms_output.put_line('3');
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
